--    Author    : ChenErHao
--    Name      : ODS.LOAB.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-12-04  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ODS.LOAB PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG)
SELECT 
     FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE
    ,DATA_ORG_CODE
    ,CUSTOMER_TYPE
    ,CUSTOMER_ID
    ,LPIC_SUB
    ,REGIST_ADD_CODE
    ,ENTERPRISE_INVESTOR
    ,ENTERPRISE_SCALE
    ,LOAN_IOUS_CODE
    ,PRODUCT_CATEGORY
    ,LOANS_ACTUALLY
    ,LENDING_DATE
    ,LOAN_DUE_DATE
    ,ROLL_OVER_DATE
    ,LOAN_CUR_TYPE
    ,LOAN_BALANCE
    ,RATE_FIXED_FLOAT
    ,RATE_LEVEL
    ,GUARANTY_STYLE
    ,LOAN_QUALITY
    ,LOAN_STATUS 
    ,DATA_SRC_ORG
FROM STG.LOAB 
WHERE DATA_DATE='#V_DATA_DATE#'

UNION ALL

SELECT 
     FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE
    ,DATA_ORG_CODE
    ,CUSTOMER_TYPE
    ,CUSTOMER_ID
    ,LPIC_SUB
    ,REGIST_ADD_CODE
    ,ENTERPRISE_INVESTOR
    ,ENTERPRISE_SCALE
    ,LOAN_IOUS_CODE
    ,PRODUCT_CATEGORY
    ,LOANS_ACTUALLY
    ,LENDING_DATE
    ,LOAN_DUE_DATE
    ,ROLL_OVER_DATE
    ,LOAN_CUR_TYPE
    ,LOAN_BALANCE
    ,RATE_FIXED_FLOAT
    ,RATE_LEVEL
    ,GUARANTY_STYLE
    ,LOAN_QUALITY
    ,LOAN_STATUS 
    ,DATA_SRC_ORG
FROM STG.LOAB_13 T 
WHERE DATA_DATE='#V_DATA_DATE#'
;